-- EXEC SP_Load_TapeReel_FG

ALTER Proc SP_Load_TapeReel_FG AS
BEGIN
	DECLARE @CLASSSTORE_CURSOR CURSOR
	DECLARE @CLASSSTORE_PROCESSSTEP_ID INT
	DECLARE @TR_PROCESSSTEP_ID INT
	DECLARE @TR_PART_NUMBER VARCHAR(50)

	DECLARE @RESULTS TABLE ( PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50) )

	SET @CLASSSTORE_CURSOR = CURSOR FOR SELECT PS.PROCESSSTEP_ID FROM PROCESS_STEP PS, PRODUCT P WHERE PS.ProcessStep_ID = P.ProcessStep_ID AND PS_TYPE = 'Tape & Reel' and PS_TYPE_NAME = 'CLASS STORE'

	OPEN @CLASSSTORE_CURSOR
	FETCH NEXT FROM @CLASSSTORE_CURSOR INTO @CLASSSTORE_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		SELECT @TR_PART_NUMBER = P.Part_Number, @TR_PROCESSSTEP_ID = PS.ProcessStep_ID FROM Product P, Process_Step PS 
			WHERE P.ProcessStep_Id = PS.ProcessStep_ID AND P.PROCESSSTEP_ID = ( SELECT TOP 1 NEXT_PROCESSSTEP_ID FROM PROCESS_sTEP_TRAVERSE_TABLE WHERE PROCESSSTEP_ID = @CLASSSTORE_PROCESSSTEP_ID ) 
			AND PS.PS_Type_Name = 'Finished Goods'
		INSERT INTO @RESULTS ( PROCESSSTEP_ID, PART_NUMBER )VALUES( @TR_PROCESSSTEP_ID, @TR_PART_NUMBER )
		FETCH NEXT FROM @CLASSSTORE_CURSOR INTO @CLASSSTORE_PROCESSSTEP_ID
	END
	SELECT * FROM @RESULTS ORDER BY PART_NUMBER
END